<?php
/**
 * Created by PhpStorm.
 * User: 小灰灰
 * Date: 2022-12-12
 * Time: 17:20:49
 * Info:
 */

namespace support\lib;

use PDO;
use support\exception\BusinessException;
use ZipArchive;
use \Exception;
use support\lib\Env;
use think\facade\Db;

class Sql
{

    public static function table_schema()
    {
        $DataBase = config('thinkorm');

        return $DataBase['connections']['mysql']['database'];
    }

    /**
     * 根据表名获取表结构sql
     */
    public static function getTableSql($table)
    {
        $name = $table;
        $row  = Db::query("SHOW CREATE TABLE `{$name}`");
        $row  = array_values($row[0]);
        $info = $row[1];

        return "<style>pre {display: block;font-family: Monaco,Menlo,Consolas,\"Courier New\",monospace;padding: 9.5px;margin-bottom: 10px;font-size: 12px;line-height: 20px;word-break: break-all;word-wrap: break-word;white-space: pre;white-space: pre-wrap;background-color: #f5f5f5;border: 1px solid #ccc;border-radius: 4px;color: #333;}</style> <pre>{$info};</pre>";
    }

    /**
     * 修改表名
     *
     * @param $from
     * @param $to
     *
     * @return void
     */
    public static function rename($from, $to)
    {
        $sql = "alter table {$from} rename as {$to};";
        $res = Db::query($sql);

        return $res;

    }

    /**
     * 修改表注释
     * @return void
     */
    public static function updateComments($table_name, $table_comment)
    {
        $sql = "ALTER TABLE `{$table_name}` COMMENT '{$table_comment}'";
        $res = Db::query($sql);

        return $res;
    }

    /**
     * 按表获取摘要
     *
     * @param      $table
     * @param null $section
     *
     * @return array|mixed
     * @throws BusinessException
     */
    public static function getSchema($table, $section = null, $value_key = 1)
    {
        $database   = self::table_schema();
        $sql        = "select * from information_schema.COLUMNS where TABLE_SCHEMA = '{$database}' and table_name = '{$table}'";
        $schema_raw = $section !== 'table' ? Db::query($sql) : [];
        $forms      = [];
        $columns    = [];
        foreach ($schema_raw as $item) {
            $field      = $item['COLUMN_NAME'];
            $columnsArr = [
                'field'          => $field,
                'type'           => self::typeToMethod($item['DATA_TYPE'],
                    (bool)strpos($item['COLUMN_TYPE'], 'unsigned')),
                'comment'        => $item['COLUMN_COMMENT'],
                'default'        => $item['COLUMN_DEFAULT'],
                'length'         => static::getLengthValue($item),
                'nullable'       => $item['IS_NULLABLE'] !== 'NO',
                'primary_key'    => $item['COLUMN_KEY'] === 'PRI',
                'auto_increment' => strpos($item['EXTRA'], 'auto_increment') !== false
            ];
            if ($value_key) {
                $columns[] = $columnsArr;
            } else {
                $columns[$field] = $columnsArr;
            }

            $forms[$field] = [
                'field'        => $field,
                'comment'      => $item['COLUMN_COMMENT'],
                'control'      => static::typeToControl($item['DATA_TYPE']),
                'form_show'    => $item['COLUMN_KEY'] !== 'PRI',
                'list_show'    => true,
                'enable_sort'  => false,
                'searchable'   => false,
                'search_type'  => 'normal',
                'control_args' => '',
            ];
        }
        $table_schema = $section == 'table' || ! $section ? Db::query("SELECT TABLE_COMMENT FROM  information_schema.`TABLES` WHERE  TABLE_SCHEMA='{$database}' and TABLE_NAME='{$table}'") : [];
        $indexes      = ! $section || in_array($section,
            ['keys', 'table']) ? Db::query("SHOW INDEX FROM `{$table}`") : [];
        $keys         = [];
        $primary_key  = [];
        foreach ($indexes as $index) {
            $key_name = $index['Key_name'];
            if ($key_name == 'PRIMARY') {
                $primary_key[] = $index['Column_name'];
                continue;
            }
            if ( ! isset($keys[$key_name])) {
                $keys[$key_name] = [
                    'name'    => $key_name,
                    'columns' => [],
                    'type'    => $index['Non_unique'] == 0 ? 'unique' : 'normal'
                ];
            }
            $keys[$key_name]['columns'][] = $index['Column_name'];
        }

        $data = [
            'table'   => [
                'name'        => $table,
                'comment'     => $table_schema[0]['TABLE_COMMENT'] ?? '',
                'primary_key' => $primary_key
            ],
            'columns' => $columns,
            'forms'   => $forms,
            'keys'    => array_reverse($keys, true)
        ];

        $schema          = Db::name('options')->where('name', "table_form_schema_$table")->value('value');
        $form_schema_map = $schema ? json_decode($schema, true) : [];

        foreach ($data['forms'] as $field => $item) {
            if (isset($form_schema_map[$field])) {
                $data['forms'][$field] = $form_schema_map[$field];
            }
        }

        if ($value_key == 1) {
            $data['keys']  = array_values($data['keys']);
            $data['forms'] = array_values($data['forms']);
        }

        return $section ? $data[$section] : $data;
    }

    /**
     * 获取控件参数
     *
     * @param $control
     * @param $control_args
     *
     * @return array
     */
    static public function getControlProps($control, $control_args): array
    {
        if ( ! $control_args) {
            return [];
        }
        $control = strtolower($control);
        $props   = [];
        $split   = explode(';', $control_args);
        foreach ($split as $item) {
            $pos = strpos($item, ':');
            if ($pos === false) {
                continue;
            }
            $name   = trim(substr($item, 0, $pos));
            $values = trim(substr($item, $pos + 1));
            // values = a:v,c:d
            $pos = strpos($values, ':');
            if ($pos !== false) {
                $options = explode(',', $values);
                $values  = [];
                foreach ($options as $option) {
                    [$v, $n] = explode(':', $option);
                    if (in_array($control,
                            ['select', 'selectmulti', 'treeselect', 'treemultiselect']) && $name == 'data') {
                        $values[] = ['value' => $v, 'name' => $n];
                    } else {
                        $values[$v] = $n;
                    }
                }
            }
            $props[$name] = $values;
        }

        return $props;

    }

    /**
     * 变量或数组中的元素只能是字母数字下划线组合
     *
     * @param $var
     *
     * @return mixed
     * @throws BusinessException
     */
    static public function filterAlphaNum($var)
    {
        $vars = (array)$var;
        array_walk_recursive($vars, function ($item) {
            if (is_string($item) && ! preg_match('/^[a-zA-Z_0-9]+$/', $item)) {
                throw new BusinessException('参数不合法');
            }
        });

        return $var;
    }

    /**
     * 数据库类型到表单类型的转换
     *
     * @param $type
     * @param $unsigned
     *
     * @return string
     */
    public static function typeToMethod($type, $unsigned = false)
    {
        if (stripos($type, 'int') !== false) {
            $type = str_replace('int', 'Integer', $type);

            return $unsigned ? "unsigned".ucfirst($type) : lcfirst($type);
        }
        $map = [
            'int'        => 'integer',
            'varchar'    => 'string',
            'mediumtext' => 'mediumText',
            'longtext'   => 'longText',
            'datetime'   => 'dateTime',
        ];

        return $map[$type] ?? $type;
    }

    /**
     * 数据库类型到插件的转换
     *
     * @param $type
     *
     * @return string
     */
    static public function typeToControl($type): string
    {
        if (stripos($type, 'int') !== false) {
            return 'inputNumber';
        }
        if (stripos($type, 'time') !== false || stripos($type, 'date') !== false) {
            return 'dateTimePicker';
        }
        if (stripos($type, 'text') !== false) {
            return 'textArea';
        }
        if ($type === 'enum') {
            return 'select';
        }

        return 'input';
    }

    /**
     * 获取字段长度或默认值
     *
     * @param $schema
     *
     * @return mixed|string
     */
    static public function getLengthValue($schema)
    {
        $type = $schema['DATA_TYPE'];

        if (in_array($type, ['float', 'decimal', 'double'])) {
            return "{$schema['NUMERIC_PRECISION']},{$schema['NUMERIC_SCALE']}";
        }
        if ($type === 'enum') {
            return implode(',', array_map(function ($item) {
                return trim($item, "'");
            }, explode(',', substr($schema['COLUMN_TYPE'], 5, -1))));
        }
        if (in_array($type, ['varchar', 'text', 'char'])) {
            return $schema['CHARACTER_MAXIMUM_LENGTH'];
        }
        if (in_array($type, ['time', 'datetime', 'timestamp'])) {
            return $schema['CHARACTER_MAXIMUM_LENGTH'];
        }
        if (in_array($type, ['int', 'tinyint'])) {
            return get_between_str($schema['COLUMN_TYPE'], "(", ")");
        }

        return '';
    }

    /**
     * 表单类型到插件的映射
     * @return \string[][]
     */
    static public function methodControlMap(): array
    {
        return [
            //method=>[控件]
            'integer' => ['InputNumber'],
            'string'  => ['Input'],
            'text'    => ['TextArea'],
            'date'    => ['DatePicker'],
            'enum'    => ['Select'],
            'float'   => ['Input'],

            'tinyInteger'   => ['InputNumber'],
            'smallInteger'  => ['InputNumber'],
            'mediumInteger' => ['InputNumber'],
            'bigInteger'    => ['InputNumber'],

            'unsignedInteger'       => ['InputNumber'],
            'unsignedTinyInteger'   => ['InputNumber'],
            'unsignedSmallInteger'  => ['InputNumber'],
            'unsignedMediumInteger' => ['InputNumber'],
            'unsignedBigInteger'    => ['InputNumber'],

            'decimal' => ['Input'],
            'double'  => ['Input'],

            'mediumText' => ['TextArea'],
            'longText'   => ['TextArea'],

            'dateTime' => ['DateTimePicker'],

            'time'      => ['DateTimePicker'],
            'timestamp' => ['DateTimePicker'],

            'char' => ['Input'],

            'binary' => ['Input'],
        ];
    }

    /**
     * 重命名索引
     *
     * @param $oldName
     * @param $newName
     *
     * @return void
     */
    static public function renameIndex($tableName, $oldName, $newName)
    {
        /*$versionV = Db::query("select VERSION() as v");
        $version  = $versionV[0]['v'];*/
        $sql = "ALTER TABLE {$tableName} RENAME INDEX '{$oldName}' TO '{$newName}'";
        Db::query($sql);
    }

    /**
     * 创建数据表
     *
     * @param $tableName    表名
     * @param $columns      字段
     *
     * @return void
     */
    static public function createTable($tableName, $columns, $comment = "")
    {
        if (empty($columns)) {
            $columns = [
                'field'          => 'id',
                '_field_id'      => 0,
                'comment'        => '主键',
                'default'        => null,
                'type'           => 'integer',
                'primary_key'    => true,
                'auto_increment' => true,
                'nullable'       => false
            ];
        }
        $columns_field = "";

        foreach ($columns as $v) {
            if ( ! $v['nullable']) {
                //不允许为空
                $null = "NOT NULL";
            } else {
                $null = 'NULL';
            }

            //是否主键：
            $primary = "";
            if ($v['primary_key']) {
                $primary = "unsigned";
            }
            //是否自增
            $autoIncrement = "";
            if ($v['auto_increment']) {
                $autoIncrement = "AUTO_INCREMENT";
            }

            $method  = $v['type'];
            $length  = $v['length'];
            $sqlType = "";
            switch ($v['type']) {
                case 'string':
                    $length  = $length ?: 255;
                    $sqlType .= "varchar($length) ";
                    break;
                case 'enum':
                    $args = array_map('trim', explode(',', (string)$v['length']));
                    foreach ($args as $key => $value) {
                        $args[$key] = $value;
                    }
                    $sqlType .= 'enum('.implode(',', $args).') ';
                    break;
                case 'double':
                case 'float':
                case 'decimal':
                    if (trim($v['length'])) {
                        $args    = array_map('intval', explode(',', $v['length']));
                        $args[1] = $args[1] ?? $args[0];
                        $sqlType .= "$method($args[0], $args[1]) ";
                        break;
                    }
                    $sqlType .= "$method ";
                    break;
                default :
                    $sqlType .= "$method ";
            }

            //默认值
            if ($autoIncrement == 'AUTO_INCREMENT' && $default == null) {
                $sql = "ALTER TABLE {$tableName} ADD `{$field}` {$sqlType} {$primary} {$null} {$autoIncrement} COMMENT '{$comment}'";
            } elseif ($method == 'text') {
                $sql = "ALTER TABLE {$tableName} ADD `{$field}` {$sqlType} {$primary} COMMENT '{$comment}'";
            } else {
                if ($default == null) {
                    $sql = "ALTER TABLE {$tableName} ADD `{$field}` {$sqlType} {$primary} {$null} {$autoIncrement} COMMENT '{$comment}'";
                } else {
                    $sql = "ALTER TABLE {$tableName} ADD `{$field}` {$sqlType} {$primary} {$null} DEFAULT '{$default}' {$autoIncrement} COMMENT '{$comment}'";
                }
            }
        }

        $sql = "CREATE TABLE `{$tableName}` ({$columns_field} PRIMARY KEY (`{$columns[0]['field']}`) USING BTREE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 AUTO_INCREMENT=1 COMMENT='{$comment}';";
        Db::execute($sql);
    }

    /**
     * 新增表字段
     *
     * @param $tableName        表名
     * @param $field            字段名
     * @param $method           字段类型
     * @param $len              长度/值
     * @param $primary_key      是否主键
     * @param $auto_increment   是否自增
     * @param $nullable         是否为空    true=允许空
     * @param $comment          字段备注
     *
     * @return true
     */
    static public function alterTableField(
        $tableName,
        $field,
        $method,
        $len,
        $default = null,
        $primary_key = false,
        $auto_increment = false,
        $nullable = true,
        $comment = ''
    ) {
        if ( ! $nullable) {
            //不允许为空
            $null = "NOT NULL";
        } else {
            $null = 'NULL';
        }

        //是否主键：
        $primary = "";
        if ($primary_key) {
            $primary = "unsigned";
        }
        //是否自增
        $autoIncrement = "";
        if ($auto_increment) {
            $autoIncrement = "AUTO_INCREMENT";
        }

        $length  = $len;
        $sqlType = "";
        switch ($method) {
            case 'string':
                $length  = $length ?: 255;
                $sqlType .= "varchar($length) ";
                break;
            case 'enum':
                $args = array_map('trim', explode(',', (string)$length));
                foreach ($args as $key => $value) {
                    $args[$key] = $value;
                }
                $sqlType .= 'enum('.implode(',', $args).') ';
                break;
            case 'double':
            case 'float':
            case 'decimal':
                if (trim($length)) {
                    $args    = array_map('intval', explode(',', $length));
                    $args[1] = $args[1] ?? $args[0];
                    $sqlType .= "$method($args[0], $args[1]) ";
                    break;
                }
                $sqlType .= "$method ";
                break;
            default :
                $sqlType .= "$method ";
        }

        //默认值
        if ($autoIncrement == 'AUTO_INCREMENT' && $default == null) {
            $sql = "ALTER TABLE {$tableName} ADD `{$field}` {$sqlType} {$primary} {$null} {$autoIncrement} COMMENT '{$comment}'";
        } elseif ($method == 'text') {
            $sql = "ALTER TABLE {$tableName} ADD `{$field}` {$sqlType} {$primary} COMMENT '{$comment}'";
        } else {
            if ($default == null) {
                $sql = "ALTER TABLE {$tableName} ADD `{$field}` {$sqlType} {$primary} {$null} {$autoIncrement} COMMENT '{$comment}'";
            } else {
                $sql = "ALTER TABLE {$tableName} ADD `{$field}` {$sqlType} {$primary} {$null} DEFAULT '{$default}' {$autoIncrement} COMMENT '{$comment}'";
            }
        }
        try {
            Db::execute($sql);
        } catch (\Exception $e) {
            throw new BusinessException($e->getMessage());
        }
    }

    /**
     * 检测是否是合法Path
     *
     * @param $var
     *
     * @return string
     * @throws BusinessException
     */
    static public function filterPath($var): string
    {
        if ( ! is_string($var) || ! preg_match('/^[a-zA-Z0-9_\-\/]+$/', $var)) {
            throw new BusinessException('参数不合法');
        }

        return $var;
    }

    /**
     * 字段类型到php类型映射
     *
     * @param string $type
     *
     * @return string
     */
    static public function getType(string $type): string
    {
        if (strpos($type, 'int') !== false) {
            return 'integer';
        }
        switch ($type) {
            case 'varchar':
            case 'string':
            case 'text':
            case 'date':
            case 'time':
            case 'guid':
            case 'datetimetz':
            case 'datetime':
            case 'decimal':
            case 'enum':
                return 'string';
            case 'boolean':
                return 'integer';
            case 'float':
                return 'float';
            default:
                return 'mixed';
        }
    }

    /**
     * 获取表名主键
     * @return void
     */
    static public function getKeyName($table)
    {
        $database = self::table_schema();
        $sql      = "select COLUMN_NAME,DATA_TYPE,COLUMN_KEY,COLUMN_COMMENT from INFORMATION_SCHEMA.COLUMNS where table_name = '{$table}' and table_schema = '{$database}'";
        $sqlList  = Db::query($sql);
        foreach ($sqlList as $item) {
            if ($item['COLUMN_KEY'] === 'PRI') {
                return $item['COLUMN_NAME'];
            }
        }
    }

    /**
     * 类转换为url path
     *
     * @param $controller_class
     *
     * @return false|string
     */
    static function controllerToUrlPath($controller_class)
    {
        $key    = strtolower($controller_class);
        $action = '';
        if (strpos($key, '@')) {
            [$key, $action] = explode('@', $key, 2);
        }
        $prefix = 'plugin';
        $paths  = explode('\\', $key);
        if (count($paths) < 2) {
            return false;
        }
        $base = '';
        if (strpos($key, "$prefix\\") === 0) {
            if (count($paths) < 4) {
                return false;
            }
            array_shift($paths);
            $plugin = array_shift($paths);
            $base   = "/app/$plugin/";
        }
        array_shift($paths);
        foreach ($paths as $index => $path) {
            if ($path === 'controller') {
                unset($paths[$index]);
            }
        }
        $suffix = 'controller';
        $code   = $base.implode('/', $paths);
        if (substr($code, -strlen($suffix)) === $suffix) {
            $code = substr($code, 0, -strlen($suffix));
        }
        $adminFix = substr($code, 0, 6);
        if ($adminFix === 'admin/') {
            $code = substr($code, 6);
        }

        return $action ? "$code/$action" : $code;
    }

    /**
     * 密码哈希
     *
     * @param $password
     * @param $algo
     *
     * @return false|string|null
     */
    static public function passwordHash($password, $algo = PASSWORD_DEFAULT)
    {
        return password_hash($password, $algo);
    }

    /**
     * 验证密码哈希
     *
     * @param $password
     * @param $hash
     *
     * @return bool
     */
    static public function passwordVerify($password, $hash): bool
    {
        return password_verify($password, $hash);
    }

}
